{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "#!/usr/bin/python\n",
    "# -*- coding: utf-8 -*\n",
    "\n",
    "import paho.mqtt.client as mqtt\n",
    "import json\n",
    "import pymysql\n",
    "import time\n",
    "\n",
    "\n",
    "load = {}\n",
    "def gettime():\n",
    "    time1=time.strftime(\"%Y-%m-%d %H:%M:%S\",time.localtime())\n",
    "    return time1\n",
    "\n",
    "# 服务器地址\n",
    "host = 'broker.emqx.io'\n",
    "# 通信端口 默认端口1883\n",
    "port = 1883\n",
    "\n",
    "\n",
    "#username = 'username '\n",
    "#password = 'password '\n",
    "\n",
    "# 订阅主题名\n",
    "topic = 'test_topic'\n",
    "\n",
    "\n",
    "# 连接后事件\n",
    "def on_connect(client, userdata, flags, respons_code):\n",
    "    if respons_code == 0:\n",
    "        # 连接成功\n",
    "        print('Connection Succeed!')\n",
    "    else:\n",
    "        # 连接失败并显示错误代码\n",
    "        print('Connect Error status {0}'.format(respons_code))\n",
    "    # 订阅信息\n",
    "    client.subscribe(topic)\n"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "cd7effeab01601c3"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 接收到数据后事件\n",
    "def on_message(client, userdata, msg):\n",
    "    #打印订阅消息主题\n",
    "    print(\"topic\", msg.topic)\n",
    "\n",
    "\n",
    "    # 打印消息数据\n",
    "    # 补充内容\n",
    "    jsondata = json.loads(msg.payload)\n",
    "    load = jsondata[\"msg\"]\n",
    "    a = load[\"a\"]\n",
    "    b = load[\"b\"]\n",
    "\n",
    "    print(msg.payload)\n",
    "    print(\"msg payload\", jsondata)\n",
    "    print(a,b)\n",
    "\n",
    "def main():\n",
    "    client = mqtt.Client()\n",
    "    # 注册事件\n",
    "    client.on_connect = on_connect\n",
    "    client.on_message = on_message\n",
    "    # 设置账号密码（如果需要的话）\n",
    "    #client.username_pw_set(username, password=password)\n",
    "    # 连接到服务器\n",
    "    client.connect(host, port=port, keepalive=60)\n",
    "    # 守护连接状态\n",
    "    client.loop_forever()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ab09cfdb56d39f07"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "#MySQL保存\n",
    "def sqlsave(jsonData):\n",
    "      # 打开数据库连接\n",
    "\n",
    "    DBHOST = 'localhost'\n",
    "    DBUSER = 'root'\n",
    "    DBPASS = 'guotai'\n",
    "    DBNAME = 'mqtt_test'\n",
    "\n",
    "    try:\n",
    "        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)\n",
    "        print('数据库连接成功!')\n",
    "    except pymysql.Error as e:\n",
    "        print('数据库连接失败' + str(e))\n",
    "\n",
    "    #\n",
    "    # db = pymysql.connect(host='localhost',\n",
    "    #                        user='root',\n",
    "    #                        password='guotai',\n",
    "    #                        database='mqtt_test')\n",
    "   # db = pymysql.connect(host=\"192.168.3.228\",user=\"root\",password=\"guotai\",database=\"mqtt_test\",charset='utf8')\n",
    "    # 使用cursor()方法获取操作游标\n",
    "    cursor = db.cursor()\n",
    "    # SQL 插入语句\n",
    "    try:\n",
    "        sql = \"INSERT INTO test (get_time,a,b)  VALUES ('%s','%s','%s');\"\n",
    "        value = (gettime(),a,b)\n",
    "        cursor.execute(sql)\n",
    "        db.commit()\n",
    "        print(\"数据库保存成功！\")\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "\n",
    "    # 关闭数据库连接\n",
    "    db.close()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "      # DBHOST = 'localhost'\n",
    "      # DBUSER = 'root'\n",
    "      # DBPASS = 'guotai'\n",
    "      # DBNAME = 'mqtt_test'\n",
    "      #\n",
    "      # try:\n",
    "      #     db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)\n",
    "      #     print('数据库连接成功!')\n",
    "      # except pymysql.Error as e:\n",
    "      #     print('数据库连接失败' + str(e))\n",
    "\n",
    "      # cur = db.cursor()\n",
    "      # cur.execute('DROP TABLE IF EXISTS Student')\n",
    "      # sqlQuery = \"CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age int )\"\n",
    "\n",
    "      # cur.execute(sqlQuery)\n",
    "      #\n",
    "      # sqlQuery = \" INSERT INTO test (Name, Email, Age) VALUE (%s,%s,%s) \"\n",
    "      # value = ('Mike', '123456@163.com', 20)\n",
    "      #\n",
    "      # try:\n",
    "      #     cur.execute(sqlQuery, value)\n",
    "      #     db.commit()\n",
    "      #     print('数据插入成功！')\n",
    "      # except pymysql.Error as e:\n",
    "      #     print(\"数据插入失败：\" + e)\n",
    "      #     db.rollback()\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    main()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "33dc148927920a31"
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "language": "python",
   "display_name": "Python 3 (ipykernel)"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
